USE [ReTS_Db]
GO 

PRINT ''
PRINT 'Creating stored procedures for Requirement table...'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SelectRequirementById]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_SelectRequirementById]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_SelectRequirementById]
    @Requirement_Id int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM [Requirement] WHERE Requirement_Id = @Requirement_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_InsertRequirement]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_InsertRequirement]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_InsertRequirement]
    @Title nvarchar(50),
	@Description nvarchar(50),
	@Version_Number nvarchar(50),
	@Parent_Requirement_Id int,
	@Project_Id int,
	@Phase_Id int, 
	@Hierarchy_Id int,
	@Estimated_Time_to_Complete decimal(4,1),
	@Is_Complete bit
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [Requirement] 
	(
	Title,
	Description,
	Version_Number,
	Parent_Requirement_Id,
	Project_Id,
	Phase_Id,
	Hierarchy_Id,
	Estimated_Time_to_Complete,
	Is_Complete
	)
VALUES 
	(
	@Title,
	@Description,
	@Version_Number,
	@Parent_Requirement_Id,
	@Project_Id,
	@Phase_id, 
	@Hierarchy_Id,
	@Estimated_Time_to_Complete,
	@Is_Complete
	)

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_UpdateRequirement]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_UpdateRequirement]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_UpdateRequirement]
    @Requirement_Id int,
    @Title nvarchar(50),
	@Description nvarchar(50),
	@Version_Number nvarchar(50),
	@Parent_Requirement_Id int,
	@Project_Id int,
	@Phase_Id int, 
	@Hierarchy_Id int,
	@Estimated_Time_to_Complete decimal(4,1),
	@Is_Complete bit
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Requirement] 
SET 
	Title = @Title,
	Description = @Description,
	Version_Number = @Version_Number,
	Parent_Requirement_Id = @Parent_Requirement_Id,
	Project_Id = @Project_Id,
	Phase_Id = @Phase_Id,
	Hierarchy_Id = @Hierarchy_Id,
	Estimated_Time_to_Complete = @Estimated_Time_to_Complete,
	Is_Complete = @Is_Complete
WHERE Requirement_Id = @Requirement_Id

END
'
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteRequirement]') AND type in (N'P', N'PC'))

BEGIN
DROP PROCEDURE [dbo].[usp_DeleteRequirement]
END


EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[usp_DeleteRequirement]
    @Requirement_Id int
AS
BEGIN
SET NOCOUNT ON;

DELETE
FROM [Requirement] 
WHERE Requirement_Id = @Requirement_Id

END
'
GO 